﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Data.Common;

using Ivy.Common.Framework.Base;
using Ivy.Common.Framework.DataAccess;
using Ivy.UserRegistration.Entity;

using Hsmp.SocietyOperation.Entity;

using Hsmp.SocietyAdmin.Entity;

namespace Hsmp.SocietyOperation.DataAccess
{
    public class SocietyManagementMemberRegisterDao : IDaoInterface<SocietyManagementMemberRegister, SocietyManagementMemberRegisterCriteria>
    {
        #region  Variable & constant
        #endregion  Variable & constant

        #region Constructor & Destructor
        public SocietyManagementMemberRegisterDao() : base() { }
        #endregion Constructor & Destructor

        #region Properties
        #endregion Properties

        #region Public Methods
        #region IDaoInterface<SocietyManagementMemberRegister, SocietyManagementMemberRegisterCriteria> Members
        public bool Insert(SocietyManagementMemberRegister SocietyManagementMemberRegisterEntity)
        {

            {
                bool IsOperationSuccessful = false;
                try
                {
                    if (SocietyManagementMemberRegisterEntity.EntityState == EntityState.New)
                    {
                        DbConnection DbConn = DataAccessProviderUtility.CreateConnectionWithConnectionString();
                        DbCommand CmdObj = DataAccessProviderUtility.CreateCommand();
                        CmdObj.CommandText = "Ivy_SocietyManagementMemberRegister_Insert";
                        //CmdObj.CommandText = UserManagementSQLCommandText.InsertUser();
                        CmdObj.CommandType = CommandType.StoredProcedure;
                        //CmdObj.CommandType = CommandType.Text;

                        DataAccessProviderUtility.AddParameter(CmdObj, "@MemberUserKey", ParameterDirection.Input, DbType.Int32, SocietyManagementMemberRegisterEntity.MemberUserKey);
                        DataAccessProviderUtility.AddParameter(CmdObj, "@MemberSocietyKey", ParameterDirection.Input, DbType.Int32, SocietyManagementMemberRegisterEntity.MemberSocietyKey);
                        //DataAccessProviderUtility.AddParameter(CmdObj, "@UserLoginID", ParameterDirection.Input, DbType.String, SocietyManagementMemberRegisterEntity.UserLoginID);
                        //DataAccessProviderUtility.AddParameter(CmdObj, "@SocietyName", ParameterDirection.Input, DbType.String, SocietyManagementMemberRegisterEntity.SocietyName);
                        DataAccessProviderUtility.AddParameter(CmdObj, "@StartDateOfMembership", ParameterDirection.Input, DbType.DateTime, SocietyManagementMemberRegisterEntity.StartDateOfMembership);
                        DataAccessProviderUtility.AddParameter(CmdObj, "@EndDateOfMembership", ParameterDirection.Input, DbType.DateTime, SocietyManagementMemberRegisterEntity.EndDateOfMembership);
                        

                        DatabaseCommon.InsertStoredProcForParameterDatEntityBase(CmdObj, SocietyManagementMemberRegisterEntity);

                       
                        DataAccessProviderUtility.AddParameter(CmdObj, "@Sys_DateOfCreation", ParameterDirection.Output, DbType.DateTime, DBNull.Value);

                        CmdObj.Connection = DbConn;
                        DbConn.Open();
                        CmdObj.Prepare();
                        int _NoOfRecords = CmdObj.ExecuteNonQuery();
                        if (_NoOfRecords == 1)
                        {
                            SocietyManagementMemberRegisterEntity.MemberUserKey = Convert.ToInt32(CmdObj.Parameters["@MemberUserKey"].Value);
                            SocietyManagementMemberRegisterEntity.MemberSocietyKey = Convert.ToInt32(CmdObj.Parameters["@MemberSocietyKey"].Value);
                            SocietyManagementMemberRegisterEntity.Sys_DateOfCreation = Convert.ToDateTime(CmdObj.Parameters["@Sys_DateOfCreation"].Value);
                            SocietyManagementMemberRegisterEntity.Sys_DateOfModification = SocietyManagementMemberRegisterEntity.Sys_DateOfCreation;
                            SocietyManagementMemberRegisterEntity.EntityState = EntityState.UnChanged;
                            IsOperationSuccessful = true;
                        }
                        CmdObj.Dispose();
                        DbConn.Close();
                    }
                    else
                    {
                        throw new Exception("For inserting new User entity data to database, [EntityState] attribute should be [New]");
                    }
                }
                catch (Exception ex)
                {
                    System.Diagnostics.Debug.Print(ex.Message);
                    throw ex;
                }
                return IsOperationSuccessful;
            }
        }

        public bool Update(SocietyManagementMemberRegister SocietyManagementMemberRegisterEntity)
        {

            {
                bool IsOperationSuccessful = false;
                try
                {
                    if (SocietyManagementMemberRegisterEntity.EntityState == EntityState.Changed)
                    {
                        DbConnection DbConn = DataAccessProviderUtility.CreateConnectionWithConnectionString();
                        DbCommand CmdObj = DataAccessProviderUtility.CreateCommand();
                        CmdObj.CommandText = "Ivy_SocietyManagementMemberRegister_Update";
                        CmdObj.CommandType = CommandType.StoredProcedure;

                        DataAccessProviderUtility.AddParameter(CmdObj, "@MemberUserKey", ParameterDirection.Input, DbType.Int32, SocietyManagementMemberRegisterEntity.MemberUserKey);
                        DataAccessProviderUtility.AddParameter(CmdObj, "@MemberSocietyKey", ParameterDirection.Input, DbType.Int32, SocietyManagementMemberRegisterEntity.MemberSocietyKey);
                        DataAccessProviderUtility.AddParameter(CmdObj, "@UserLoginID", ParameterDirection.Input, DbType.String, SocietyManagementMemberRegisterEntity.UserLoginID);
                        DataAccessProviderUtility.AddParameter(CmdObj, "@SocietyName", ParameterDirection.Input, DbType.String, SocietyManagementMemberRegisterEntity.SocietyName);
                        DataAccessProviderUtility.AddParameter(CmdObj, "@StartDateOfMembership", ParameterDirection.Input, DbType.DateTime, SocietyManagementMemberRegisterEntity.StartDateOfMembership);
                        DataAccessProviderUtility.AddParameter(CmdObj, "@EndDateOfMembership", ParameterDirection.Input, DbType.DateTime, SocietyManagementMemberRegisterEntity.EndDateOfMembership);
                       

                        DatabaseCommon.UpdateStoredProcForParameterDatEntityBase(CmdObj, SocietyManagementMemberRegisterEntity);

                        DataAccessProviderUtility.AddParameter(CmdObj, "@Sys_DateOfModification", ParameterDirection.Output, DbType.DateTime, DBNull.Value);

                        CmdObj.Connection = DbConn;
                        DbConn.Open();
                        CmdObj.Prepare();
                        int _NoOfRecords = CmdObj.ExecuteNonQuery();
                        if (_NoOfRecords == 1)
                        {
                            SocietyManagementMemberRegisterEntity.Sys_DateOfModification = Convert.ToDateTime(CmdObj.Parameters["@Sys_DateOfModification"].Value);
                            SocietyManagementMemberRegisterEntity.EntityState = EntityState.UnChanged;
                            IsOperationSuccessful = true;
                        }

                        CmdObj.Dispose();
                        DbConn.Close();
                    }
                    else
                    {
                        throw new Exception("For Updating existing User entity data to database, [EntityState] attribute should be [Changed]");
                    }
                }
                catch (Exception ex)
                {
                    System.Diagnostics.Debug.Print(ex.Message);
                    throw ex;
                }
                return IsOperationSuccessful;
            }
        }

        public bool Delete(SocietyManagementMemberRegister DataEntity)
        {
            throw new NotImplementedException();
        }

        public SocietyManagementMemberRegister GetByID(string ID)
        {
            throw new NotImplementedException();
            //{
            //    SocietyManagementMemberRegister lSelectedSocietyManagementMemberRegister = null; //IMPORTANT NOTE:- Just declare the variable never create an instance, create instance only when required.
            //    try
            //    {
            //        DbConnection DbConn = DataAccessProviderUtility.CreateConnectionWithConnectionString();
            //        DbCommand CmdObj = DataAccessProviderUtility.CreateCommand();
            //        CmdObj.CommandText = "Ivy_SocietyManagementMemberRegister_GetByUserKey";
            //        CmdObj.CommandType = CommandType.StoredProcedure;

            //        DataAccessProviderUtility.AddParameter(CmdObj, "@UserKey", ParameterDirection.Input, DbType.Int32, Convert.ToInt32(ID));

            //        CmdObj.Connection = DbConn;
            //        DbConn.Open();
            //        CmdObj.Prepare();
            //        DbDataReader _DataReader = CmdObj.ExecuteReader();

            //        if (_DataReader.HasRows)
            //        {
            //            if (_DataReader.Read())
            //            {
            //                lSelectedSocietyManagementMemberRegister = new SocietyManagementMemberRegister(); //IMPORTANT NOTE:- Create instance only when required.
            //                TranslateSocietyManagementMemberRegisterFieldsFromReaderToEntity(lSelectedSocietyManagementMemberRegister, _DataReader);
            //            }
            //        }
            //        else
            //        {
            //            throw new Exception("No User found");
            //        }
            //        CmdObj.Dispose();
            //        DbConn.Close();
            //    }
            //    catch (Exception ex)
            //    {
            //        System.Diagnostics.Debug.Print(ex.Message);
            //        throw ex;
            //    }
            //    return lSelectedSocietyManagementMemberRegister;
            //}
        }

        public List<SocietyManagementMemberRegister> GetAll()
        {
            List<SocietyManagementMemberRegister> lSocietyManagementMemberRegisterList = new List<SocietyManagementMemberRegister>();
            try
            {
                DbConnection DbConn = DataAccessProviderUtility.CreateConnectionWithConnectionString();
                DbCommand CmdObj = DataAccessProviderUtility.CreateCommand();
                CmdObj.CommandText = "Ivy_SocietyManagementMemberRegister_GetAll";
                CmdObj.CommandType = CommandType.StoredProcedure;

                CmdObj.Connection = DbConn;
                DbConn.Open();
                CmdObj.Prepare();

                DbDataReader _DataReader = CmdObj.ExecuteReader();
                if (_DataReader.HasRows)
                {
                    while (_DataReader.Read())
                    {
                        var lSocietyManagementMemberRegistertemp = new SocietyManagementMemberRegister(); //IMPORTANT NOTE:- Create instance only when required.
                        this.TranslateSocietyManagementMemberRegisterFieldsFromReaderToEntity(lSocietyManagementMemberRegistertemp, _DataReader);
                        lSocietyManagementMemberRegisterList.Add(lSocietyManagementMemberRegistertemp);
                    }
                }
                CmdObj.Dispose();
                DbConn.Close();
            }
            catch (Exception ex)
            {
                System.Diagnostics.Debug.Print(ex.Message);
                throw ex;
            }

            return lSocietyManagementMemberRegisterList;
        }

        public List<SocietyManagementMemberRegister> GetByCriteria(SocietyManagementMemberRegisterCriteria CriteriaEntity)
        {
            throw new NotImplementedException();
        }
        #endregion 

        public List<SocietyManagementMemberRegister> GetManagementMembersBySocietyKey(int SocietyKey)
        {
            List<SocietyManagementMemberRegister> _SocietyManagementMemberRegisterList = new List<SocietyManagementMemberRegister>();
            try
            {
                DbConnection DbConn = DataAccessProviderUtility.CreateConnectionWithConnectionString();
                DbCommand CmdObj = DataAccessProviderUtility.CreateCommand();
                CmdObj.CommandText = "Ivy_SocietyManagementMemberRegister_GetBySocietyKey";
                CmdObj.CommandType = CommandType.StoredProcedure;

                DataAccessProviderUtility.AddParameter(CmdObj, "@MemberSocietyKey", ParameterDirection.Input, DbType.Int32, SocietyKey);

                CmdObj.Connection = DbConn;
                DbConn.Open();
                CmdObj.Prepare();

                DbDataReader _DataReader = CmdObj.ExecuteReader();
                if (_DataReader.HasRows)
                {
                    while (_DataReader.Read())
                    {
                        SocietyManagementMemberRegister _TempSocietyManagementMemberRegister = new SocietyManagementMemberRegister(); //IMPORTANT NOTE:- Create instance only when required.
                        this.TranslateSocietyManagementMemberRegisterFieldsFromReaderToEntity(_TempSocietyManagementMemberRegister, _DataReader);
                        _SocietyManagementMemberRegisterList.Add(_TempSocietyManagementMemberRegister);
                    }
                }
                CmdObj.Dispose();
                DbConn.Close();


            }
            catch (Exception ex)
            {
                System.Diagnostics.Debug.Print(ex.Message);
                throw ex;
            }
            return _SocietyManagementMemberRegisterList; 
        }

        public SocietyManagementMemberRegister GetManagementMemberBySocietyKeyAndUserKey(int SocietyKey, int UserKey)
        {
            SocietyManagementMemberRegister _SelectedSocietyManagementMember = null; //IMPORTANT NOTE:- Just declare the variable never create an instance, create instance only when required.
            try
            {
                DbConnection DbConn = DataAccessProviderUtility.CreateConnectionWithConnectionString();
                DbCommand CmdObj = DataAccessProviderUtility.CreateCommand();
                CmdObj.CommandText = "Ivy_SocietyManagementMemberRegister_GetByUserKeySocietyKey";
                CmdObj.CommandType = CommandType.StoredProcedure;

                DataAccessProviderUtility.AddParameter(CmdObj, "@MemberSocietyKey", ParameterDirection.Input, DbType.Int32, Convert.ToInt32(SocietyKey));
                DataAccessProviderUtility.AddParameter(CmdObj, "@MemberUserKey", ParameterDirection.Input, DbType.Int32, Convert.ToInt32(UserKey));
               

                CmdObj.Connection = DbConn;
                DbConn.Open();
                CmdObj.Prepare();
                DbDataReader _DataReader = CmdObj.ExecuteReader();

                if (_DataReader.HasRows)
                {
                    if (_DataReader.Read())
                    {
                        _SelectedSocietyManagementMember = new SocietyManagementMemberRegister(); //IMPORTANT NOTE:- Create instance only when required.
                        TranslateSocietyManagementMemberRegisterFieldsFromReaderToEntity(_SelectedSocietyManagementMember, _DataReader);
                    }
                }
                else
                {
                    throw new Exception("No Society Management Member found");
                }
                CmdObj.Dispose();
                DbConn.Close();
            }
            catch (Exception ex)
            {
                System.Diagnostics.Debug.Print(ex.Message);
                throw ex;
            }
            return _SelectedSocietyManagementMember;
        }

        

        //public Society GetSelectedSociety(int SocietyKey)
        //{
        //    Society _SelectedSociety = new Society(); //IMPORTANT NOTE:- Just declare the variable never create an instance, create instance only when required.
        //    try
        //    {
        //        DbConnection DbConn = DataAccessProviderUtility.CreateConnectionWithConnectionString();
        //        DbCommand CmdObj = DataAccessProviderUtility.CreateCommand();
        //        CmdObj.CommandText = "Ivy_Society_GetByID";
        //        CmdObj.CommandType = CommandType.StoredProcedure;

        //        DataAccessProviderUtility.AddParameter(CmdObj, "@SocietyKey", ParameterDirection.Input, DbType.Int32, Convert.ToInt32(SocietyKey));


        //        CmdObj.Connection = DbConn;
        //        DbConn.Open();
        //        CmdObj.Prepare();
        //        DbDataReader _DataReader = CmdObj.ExecuteReader();

        //        if (_DataReader.HasRows)
        //        {
        //            if (_DataReader.Read())
        //            {
        //                _SelectedSociety = new Society(); //IMPORTANT NOTE:- Create instance only when required.
        //                TranslateSocietyFieldsFromReaderToEntity(_SelectedSociety, _DataReader);
        //            }
        //        }
        //        else
        //        {
        //            throw new Exception("No Society found");
        //        }
        //        CmdObj.Dispose();
        //        DbConn.Close();
        //    }

        //    catch (Exception ex)
        //    {
        //        System.Diagnostics.Debug.Print(ex.Message);
        //        throw ex;
        //    }
        //    return _SelectedSociety;
        //}

        //public User GetSelectedUser(int UserKey)
        //{
        //    User _SelectedUser = new User(); //IMPORTANT NOTE:- Just declare the variable never create an instance, create instance only when required.
        //    try
        //    {
        //        DbConnection DbConn = DataAccessProviderUtility.CreateConnectionWithConnectionString();
        //        DbCommand CmdObj = DataAccessProviderUtility.CreateCommand();
        //        CmdObj.CommandText = "Ivy_User_GetByID";
        //        CmdObj.CommandType = CommandType.StoredProcedure;

        //        DataAccessProviderUtility.AddParameter(CmdObj, "@UserKey", ParameterDirection.Input, DbType.Int32, Convert.ToInt32(UserKey));


        //        CmdObj.Connection = DbConn;
        //        DbConn.Open();
        //        CmdObj.Prepare();
        //        DbDataReader _DataReader = CmdObj.ExecuteReader();

        //        if (_DataReader.HasRows)
        //        {
        //            if (_DataReader.Read())
        //            {
        //                _SelectedUser = new User(); //IMPORTANT NOTE:- Create instance only when required.
        //                TranslateUserFieldsFromReaderToEntity(_SelectedUser, _DataReader);
        //            }
        //        }
        //        else
        //        {
        //            throw new Exception("No User found");
        //        }
        //        CmdObj.Dispose();
        //        DbConn.Close();
        //    }

        //    catch (Exception ex)
        //    {
        //        System.Diagnostics.Debug.Print(ex.Message);
        //        throw ex;
        //    }
        //    return _SelectedUser;
        //}

        //public UserProfile GetSelectedUserProfile(int Userkey)
        //{
        //    UserProfile _SelectedUserProfile = new UserProfile(); //IMPORTANT NOTE:- Just declare the variable never create an instance, create instance only when required.
        //    try
        //    {
        //        DbConnection DbConn = DataAccessProviderUtility.CreateConnectionWithConnectionString();
        //        DbCommand CmdObj = DataAccessProviderUtility.CreateCommand();
        //        CmdObj.CommandText = "Ivy_UserProfile_GetByID";
        //        CmdObj.CommandType = CommandType.StoredProcedure;

        //        DataAccessProviderUtility.AddParameter(CmdObj, "@UserKey", ParameterDirection.Input, DbType.Int32, Convert.ToInt32(Userkey));


        //        CmdObj.Connection = DbConn;
        //        DbConn.Open();
        //        CmdObj.Prepare();
        //        DbDataReader _DataReader = CmdObj.ExecuteReader();

        //        if (_DataReader.HasRows)
        //        {
        //            if (_DataReader.Read())
        //            {
        //                _SelectedUserProfile = new UserProfile(); //IMPORTANT NOTE:- Create instance only when required.
        //                TranslateUserProfileFieldsFromReaderToEntity(_SelectedUserProfile, _DataReader);
        //            }
        //        }
        //        else
        //        {
        //            throw new Exception("No User found");
        //        }
        //        CmdObj.Dispose();
        //        DbConn.Close();
        //    }

        //    catch (Exception ex)
        //    {
        //        System.Diagnostics.Debug.Print(ex.Message);
        //        throw ex;
        //    }
        //    return _SelectedUserProfile;
        //}

        #endregion Public Methods

        #region Events
        #endregion Events

        #region Private Methods
        private void TranslateSocietyManagementMemberRegisterFieldsFromReaderToEntity(SocietyManagementMemberRegister pSocietyManagementMemberRegister, DbDataReader pDbDataReader)
        {
            try
            {
                pSocietyManagementMemberRegister.MemberUserKey = Convert.ToInt32(pDbDataReader["MemberUserKey"]);
                pSocietyManagementMemberRegister.MemberSocietyKey = Convert.ToInt32(pDbDataReader["MemberSocietyKey"]);
                pSocietyManagementMemberRegister.UserLoginID = Convert.ToString(pDbDataReader["UserLoginID"]);
                pSocietyManagementMemberRegister.SocietyName = Convert.ToString(pDbDataReader["SocietyName"]);
                //pSocietyManagementMemberRegister.StartDateOfMembership = Convert.ToDateTime(pDbDataReader["StartDateOfMembership"]);
                pSocietyManagementMemberRegister.StartDateOfMembership = DateTime.Parse(Convert.ToString(pDbDataReader["StartDateOfMembership"]));
                
                if (!pDbDataReader.IsDBNull(pDbDataReader.GetOrdinal("EndDateOfMembership")))
                {
                    pSocietyManagementMemberRegister.EndDateOfMembership = DateTime.Parse(Convert.ToString(pDbDataReader["EndDateOfMembership"]));
                }
                else
                {
                    pSocietyManagementMemberRegister.EndDateOfMembership = null;
                }
  
                DatabaseCommon.TranslateSystemFieldsFromReaderToEntity(pSocietyManagementMemberRegister, pDbDataReader);
            }
            catch (Exception ex)
            {
                System.Diagnostics.Debug.Print(ex.Message);
                throw ex;
            }
        }
        
        private void TranslateSocietyFieldsFromReaderToEntity(Society pSociety, DbDataReader pDbDataReader)
        {
            try
            {
                pSociety.SocietyKey = Convert.ToInt32(pDbDataReader["SocietyKey"]);
                pSociety.SocietyCode = Convert.ToString(pDbDataReader["SocietyCode"]);
                pSociety.SocietyName = Convert.ToString(pDbDataReader["SocietyName"]);
                pSociety.SocietyAddressLine1 = Convert.ToString(pDbDataReader["SocietyAddressLine1"]);
                pSociety.SocietyAddressLine2 = Convert.ToString(pDbDataReader["SocietyAddressLine2"]);
                pSociety.SocietyAddressCity = Convert.ToString(pDbDataReader["SocietyAddressCity"]);
                pSociety.SocietyAddressState = Convert.ToString(pDbDataReader["SocietyAddressState"]);
                pSociety.SocietyAddressCountry = Convert.ToString(pDbDataReader["SocietyAddressCountry"]);
                pSociety.SocietyAddressZip = Convert.ToString(pDbDataReader["SocietyAddressZip"]);

                DatabaseCommon.TranslateSystemFieldsFromReaderToEntity(pSociety, pDbDataReader);
            }
            catch (Exception ex)
            {
                System.Diagnostics.Debug.Print(ex.Message);
                throw ex;
            }
        }
        
        private void TranslateUserFieldsFromReaderToEntity(User pUser, DbDataReader pDbDataReader)
        {
            try
            {
                pUser.UserKey = Convert.ToInt32(pDbDataReader["UserKey"]);
                pUser.UserLoginID = Convert.ToString(pDbDataReader["UserLoginID"]);
                pUser.UserFirstName = Convert.ToString(pDbDataReader["UserFirstName"]);
                pUser.UserLastName = Convert.ToString(pDbDataReader["UserLastName"]);
         
                DatabaseCommon.TranslateSystemFieldsFromReaderToEntity(pUser, pDbDataReader);
            }
            catch (Exception ex)
            {
                System.Diagnostics.Debug.Print(ex.Message);
                throw ex;
            }
        }

        private void TranslateUserProfileFieldsFromReaderToEntity(UserProfile pUserProfile, DbDataReader pDbDataReader)
        {
            try
            {
                pUserProfile.UserKey = Convert.ToInt32(pDbDataReader["UserKey"]); ;
                pUserProfile.UserFullName = Convert.ToString(pDbDataReader["UserFullName"]);
                pUserProfile.UserSex = Convert.ToString(pDbDataReader["UserSex"]);
                pUserProfile.UserDateOfBirth = Convert.ToDateTime(pDbDataReader["UserDateOfBirth"]);
                pUserProfile.UserContactNumber = Convert.ToString(pDbDataReader["UserContactNumber"]);
                pUserProfile.UserContactAddress = Convert.ToString(pDbDataReader["UserContactAddress"]);
                pUserProfile.UserAlternateEmailID = Convert.ToString(pDbDataReader["UserAlternateEmailID"]);

                DatabaseCommon.TranslateSystemFieldsFromReaderToEntity(pUserProfile, pDbDataReader);
            }
            catch (Exception ex)
            {
                System.Diagnostics.Debug.Print(ex.Message);
                throw ex;
            }
        }
        #endregion Private Methods


        
    }
}
